-- @owner: @zhanghuan96
-- @date: 2024/10/9
-- @testpoint:  bit类型性update20万条数据性能测试
@conn PrimaryDbBmode;
--step1: 创建普通表 expect:成功
drop table if exists t_bit_0028_01;
CREATE TABLE t_bit_0028_01 (
    id SERIAL PRIMARY KEY,
    bit_column BIT(1)
);

--step2: 创建存储过程向表中插入数据 expect:成功
drop procedure if exists f_insert_028_01;
CREATE OR REPLACE FUNCTION f_insert_028_01()
RETURNS VOID AS $$
DECLARE
    start_time TIMESTAMPTZ;
    end_time TIMESTAMPTZ;
    time_diff INTERVAL;
    i INTEGER;
BEGIN
    start_time := NOW();

    FOR i IN 1..200000 LOOP
        INSERT INTO t_bit_0028_01 (bit_column)
        VALUES ((RANDOM() < 0.5)::BIT(1));
    END LOOP;
    end_time := NOW();
    time_diff := end_time - start_time;

    RAISE NOTICE 'Time taken to insert 200,000 rows: %', time_diff;
END;
$$ LANGUAGE plpgsql;
/
--step3: 调用存储过程向表中插入数据， expect: 成功,并显示插入数据所用时间
call f_insert_028_01();

--step4:创建存储过程更新表中数据 expect:成功
drop procedure if exists f_insert_028_02;
CREATE OR REPLACE FUNCTION f_insert_028_02()
RETURNS VOID AS $$
DECLARE
    start_time TIMESTAMPTZ;
    end_time TIMESTAMPTZ;
    time_diff INTERVAL;
    i INTEGER;
BEGIN
    start_time := NOW();
    FOR i IN 1..200000 LOOP
        UPDATE t_bit_0028_01
        SET bit_column = (RANDOM() < 0.5)::BIT(1)
        WHERE id = i;
    END LOOP;
    end_time := NOW();
    time_diff := end_time - start_time;
    RAISE NOTICE 'Time taken to update 200,000 rows: %', time_diff;
END;
$$ LANGUAGE plpgsql;
/
--step5: 调用函数更新表中数据， expect: 成功,并显更新数据所用时间
call f_insert_028_02();

--step6: 查看数据 expect: 查看数据成功,看语句执行时间total runtime,对比修改之前的bit类型响应时间。
explain analyze select * from t_bit_0028_01;

--step7: 清理环境 expect:成功
drop table if exists t_bit_0028_01;
drop function f_insert_028_01();
drop function f_insert_028_02();
